package com.boilerCloud.dao.device;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.boilerCloud.dao.BaseDao;
import com.boilerCloud.dto.HzlqswPage;
import com.boilerCloud.dto.device.DeviceInfo;
import com.boilerCloud.dto.device.ProjectInfo;
import com.boilerCloud.util.StringUtil;
import com.mysql.jdbc.Statement;
@Repository
public class ProjectDao extends BaseDao {

	/**
	 * @Title: qryList
	 * @Description:(一句话阐述):查询项目列表
	 * @param pro 
	 * @param page 
	 * @throws Exception    
	 * @author Ran_cc
	 * @date 2018年2月1日 
	 */
	public void qryList(ProjectInfo pro, HzlqswPage<ProjectInfo> page) throws Exception {
		String countsql = "select count(DISTINCT(a.id)) from project_info a "
				+" LEFT JOIN app_user b ON a.`user_id`  =  b.id "
				+" LEFT JOIN d_device_info c ON c.`project_id` = a.`id`  "
				+" LEFT JOIN app_user d ON d.`id` = b.`dealer_id` "
				+ " where a.is_delete='n' ";
			
		String listSql = "select DISTINCT(a.id), b.name username  ,d.name dealerName,a.* from project_info a"
					+" LEFT JOIN app_user b ON a.`user_id`  =  b.id "
					+" LEFT JOIN d_device_info c ON c.`project_id` = a.`id`  "
					+" LEFT JOIN app_user d ON d.`id` = b.`dealer_id` "
					+ " where a.is_delete='n' ";
		List<Object> params= new ArrayList<Object>();
		if(!StringUtil.isEmpty(pro.getDeviceId())){
			countsql += " and c.device_no like ?";
			listSql += " and c.device_no like ?";
			params.add("%" + pro.getDeviceId() +"%");
		}
		if(!StringUtil.isEmpty(pro.getUsername())){
			countsql += " AND b.name like ? ";
			listSql += " AND b.name like ? ";
			params.add("%" + pro.getUsername() +"%");
		}
		if(!StringUtil.isEmpty(pro.getUserId())){
			countsql += " AND a.user_id = ? ";
			listSql += " AND a.user_id = ? ";
			params.add(pro.getUserId());
		}
		if(!StringUtil.isEmpty(pro.getDeviceId())){
			countsql += " AND d.id = ? ";
			listSql += " AND d.id = ? ";
			params.add(pro.getDeviceId());
		}
		if(!StringUtil.isEmpty(pro.getName())){
			countsql += " AND a.name like ? ";
			listSql += " AND a.name like ? ";
			params.add("%" + pro.getName() +"%");
		}
		if(!StringUtil.isEmpty(pro.getProjectNo())){
			countsql += " AND a.project_no  like ? ";
			listSql += " AND a.project_no  like  ? ";
			params.add("%" + pro.getProjectNo() +"%");
		}
		if(!StringUtil.isEmpty(pro.getDealerName())){
			countsql += " AND d.name like ?  ";
			listSql += "  AND d.name like ? ";
			params.add("%" + pro.getDealerName() +"%");
		}
		if(!StringUtil.isEmpty(pro.getQryAddressName())){
			countsql += " AND ( a.province LIKE ? OR a.city LIKE ? OR a.area LIKE ?  ) ";
			listSql += "  AND ( a.province LIKE ? OR a.city LIKE ? OR a.area LIKE ?) ";
			params.add("%" + pro.getQryAddressName() +"%");
			params.add("%" + pro.getQryAddressName() +"%");
			params.add("%" + pro.getQryAddressName() +"%");
		}
		
		
		if(!StringUtil.isEmpty(pro.getTypeId())&& pro.getTypeId() != 0 && pro.getTypeId() != -1){
			countsql += " and a.type_id = ?";
			listSql += " and a.type_id = ?";
			params.add(pro.getTypeId());
		}	
		listSql += " order by a.id desc ";
		this.queryListByPage(countsql, params, listSql, params, page, ProjectInfo.class);
		
	}
	public ProjectInfo qryObjectbynos() throws Exception {
			
		String listSql = "select a.* from project_info a"
					+ " where a.is_delete='n' ";
		List<Object> params= new ArrayList<Object>();
		
		
		listSql += " order by a.project_no desc  limit 1";
		ProjectInfo info = this.queryForObject(listSql, ProjectInfo.class,params.toArray());
		return info;
	}

	public void edit(ProjectInfo p) {
		List<Object> param = new ArrayList<Object>();
		String sql = "UPDATE "
					+"  boilerCloud.project_info       "
					+"SET  modify_time =  now() ";
		
		if (!StringUtil.isEmpty(p.getArea())) {
			sql+=" ,AREA = ? ";
			param.add(p.getArea());
		}
		if (!StringUtil.isEmpty(p.getCity())) {
			sql+=" ,city = ? ";
			param.add(p.getCity());
		}
		if (!StringUtil.isEmpty(p.getDealerName())) {
			sql+=" ,project_no = ? ";
			param.add(p.getDealerName());
		}
		if (!StringUtil.isEmpty(p.getName())) {
			sql+=" ,name = ? ";
			param.add(p.getName());
		}
		if (!StringUtil.isEmpty(p.getTypeId())) {
			sql+=" ,type_id = ? ";
			param.add(p.getTypeId());
		}
		if (!StringUtil.isEmpty(p.getUserId())) {
			sql+=" ,user_id = ? ";
			param.add(p.getUserId());
		}
		if (!StringUtil.isEmpty(p.getLatitude())) {
			sql+=" ,Latitude = ? ";
			param.add(p.getLatitude());
		}
		if (!StringUtil.isEmpty(p.getInstallTime())) {
			sql+=" ,InstallTime = ? ";
			param.add(p.getInstallTime());
		}
		if (!StringUtil.isEmpty(p.getLongitude())) {
			sql+=" ,Longitude = ? ";
			param.add(p.getLongitude());
		}
		if (!StringUtil.isEmpty(p.getIsDelete())) {
			sql+=" ,is_delete = ? ";
			param.add(p.getIsDelete());
		}
		if (!StringUtil.isEmpty(p.getProvince())) {
			sql+=" ,province = ? ";
			param.add(p.getProvince());
		}
		if (!StringUtil.isEmpty(p.getAddress())) {
			sql+=" ,address = ? ";
			param.add(p.getAddress());
		}
		sql+=" where id = ? ";
		param.add(p.getId());
		this.jdbcTemplate.update(sql,param.toArray());
	}

	public void del(int id) {
		String sql = "update project_info set is_delete='y' where id=?";
		this.jdbcTemplate.update(sql,id);
		
	}
	//新增项目
	public int add(final ProjectInfo pro)throws Exception {
		final String sql = "INSERT INTO project_info (project_no, NAME, type_id, user_id, latitude, longitude, install_time, create_time, modify_time, is_delete, province, city, AREA,address)"
				+ "VALUES  (?,?,?,?,?,?,?,NOW(),NOW(),'n',?,?,?,?)";
		/* List<Object> param = new ArrayList<Object>();
 		param.add(pro.getProjectNo());
 		param.add(pro.getName());
 		param.add(pro.getTypeId());
 		param.add(pro.getUserId());
 		param.add(pro.getLatitude());
 		param.add(pro.getLongitude());
 		param.add(StringUtil.StringToDate(pro.getInstallTime(), 4) );
 		param.add(pro.getProvince());
 		param.add(pro.getCity());
 		param.add(pro.getArea());
 		this.jdbcTemplate.update(sql,param.toArray());*/
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, pro.getProjectNo());
                ps.setString(2, pro.getName());
                ps.setInt(3, pro.getTypeId());
                ps.setInt(4, pro.getUserId());
                ps.setString(5, pro.getLatitude());
                ps.setString(6,pro.getLongitude());
                ps.setString(7, pro.getInstallTimeStr());
                ps.setString(8, pro.getProvince());
                ps.setString(9, pro.getCity());
                ps.setString(10, pro.getArea());
                ps.setString(11, pro.getAddress());
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
	}

	public DeviceInfo getDeviceInfo(int id)throws Exception {
		String sql = "select * from d_device_info where id = ? limit 1";
		return this.queryForObject(sql, DeviceInfo.class, id);
		
	}
	/**
	 * @Title: getProjectInfo
	 * @Description:(一句话阐述):获取单条数据的信息
	 * @param project
	 * @return    
	 * @author Ran_cc
	 * @date 2018年3月8日 
	 */
	public ProjectInfo getProjectInfo(ProjectInfo p) throws Exception {
		String sql =  " select * from project_info "; 
		List<Object> sqlParams = new  ArrayList<Object>();
		if (p!= null ) {
			sql+=" where ";
		}else
			return null;
		sql+=" 1=1 ";
		
		if (!StringUtil.isEmpty(p.getId()))
		{
			sql+= " and id = ? ";
			sqlParams.add(p.getId()+"");
		}
		if (!StringUtil.isEmpty(p.getArea())) {
			sql+=" and AREA = ? ";
			sqlParams.add(p.getArea());
		}
		if (!StringUtil.isEmpty(p.getCity())) {
			sql+=" and city = ? ";
			sqlParams.add(p.getCity());
		}
		if (!StringUtil.isEmpty(p.getProjectNo())) {
			sql+=" and project_no = ? ";
			sqlParams.add(p.getProjectNo());
		}
		if (!StringUtil.isEmpty(p.getName())) {
			sql+=" and name = ? ";
			sqlParams.add(p.getName());
		}
		if (!StringUtil.isEmpty(p.getTypeId())) {
			sql+=" and type_id = ? ";
			sqlParams.add(p.getTypeId());
		}
		if (!StringUtil.isEmpty(p.getUserId())) {
			sql+=" and user_id = ? ";
			sqlParams.add(p.getUserId());
		}
		if (!StringUtil.isEmpty(p.getLatitude())) {
			sql+=" and Latitude = ? ";
			sqlParams.add(p.getLatitude());
		}
		if (!StringUtil.isEmpty(p.getInstallTime())) {
			sql+=" and InstallTime = ? ";
			sqlParams.add(p.getInstallTime());
		}
		if (!StringUtil.isEmpty(p.getLongitude())) {
			sql+=" and Longitude = ? ";
			sqlParams.add(p.getLongitude());
		}
		if (!StringUtil.isEmpty(p.getIsDelete())) {
			sql+=" and is_delete = ? ";
			sqlParams.add(p.getIsDelete());
		}
		if (!StringUtil.isEmpty(p.getProvince())) {
			sql+=" and province = ? ";
			sqlParams.add(p.getProvince());
		}
		sql+=" limit 1  ";
		return this.queryForObject(sql, ProjectInfo.class,sqlParams);
	}

}
